﻿using System;
using System.Collections.Generic;
using System.Text;

using EasyStudy.SQLite;
using EasyStudy.ProjectUpdate.Model;
using System.Data;
using EasyStudy.Logic;

namespace EasyStudy.ProjectUpdate.DB
{
    public class ProjectDBMgr
    {
        private static string SQL_INSERT_PROJECT = "INSERT INTO Projects (NAME, COMMENT) VALUES (\"{0}\",\"{1}\");";
        private static string SQL_INSERT_SIGN = "INSERT INTO Signs (PROJECTID, COMMENT, DATE) VALUES (\"{0}\",\"{1}\", \"{2}\");";
        private static string SQL_GETALL_PROJECTS = "SELECT NAME as 项目名称, COMMENT as 备注 FROM Projects;";
        private static string SQL_GETALL_SIGNS = "SELECT COMMENT, DATE FROM Signs WHERE PROJECTID = '{0}';";
        private static SQLiteDBHelper dbHelper;

        static ProjectDBMgr()
        {
            dbHelper = new SQLiteDBHelper(EasyStudy.Logic.EbbTaskDBMgr.dbFileName);
        }

        public static List<Project> GetAllProjectsInfo()
        {
            List<Project> projInfos = new List<Project>();
            DataTable dt = dbHelper.ExecuteDataTable(SQL_GETALL_PROJECTS, null);

            foreach (DataRow dr in dt.Rows)
            { 
                Project proj = new Project();
                proj.ProjectName = dr["项目名称"].ToString();
                proj.Comment = dr["备注"].ToString();

                projInfos.Add(proj);
            }

            return projInfos;
        }

        public static void CreateAProject(string name, string comment)
        {
            string sql = string.Format(SQL_INSERT_PROJECT, name, comment);
            dbHelper.ExecuteNonQuery(sql, null);
        }

        public static void CreateASign(Sign sign)
        {
            string sql = string.Format(SQL_INSERT_SIGN, 
                sign.ProjectID,
                sign.Comment,
                EbbTaskDBMgr.FormatDateTime(sign.SignDate));
            dbHelper.ExecuteNonQuery(sql, null);
        }

        public static void CreateAProject(Project proj)
        {
            CreateAProject(proj.ProjectName, proj.Comment);
        }
    }
}
